'''
2020-12-31
增加单班次支持
'''



from typing import Text
import pymysql
import time 
import datetime
import requests
import json
from math import ceil

import win32gui, win32ui, win32con
from ctypes import windll
from PIL import Image
import cv2 as cv
import numpy as np

import cv2 as cv
import copy
from threading import Thread

stations=["1006L","TT01","1008#1","1008#2","1008#3","1010P1","1010P2","1010","1020P",
    "1020","1030P","1030","1035","1040","1050P","1050-1","1050-2","1060","1070P",
    "1070","1075","1080P","1080","1090","1095","1096U","1096L","TT02","1100P","1100",
    "1110P","1110","1115","1120P","1120","1126U","1126L","TT03","1140P1","1140P2","1140",
    "1150P","1150","1160P","1160","1165P","1165","1170P","1170","1180","1195","1190P",
    "1196U","1196L","1190","1210-3","1210-1","1210-2","1220P1","1220P2","1220","1230P",
    "1230","1235","1240P","1240","1245","1250P","1250","1260P","1260","1261P","1261",
    "1270P","1270","1275","1280","1290P","1290","1295","1300","1310P","1310","1315P",
    "1315#1","1315","1320P","1320","1330P","1330","1340P","1340","1350AP","1350A",
    "1350BP","1350B","1355","1360P","1360","1368#1","1368#2","1368","1370P","1370",
    "1380P","1380","1390P","1390","1400-1P","1400-1","1400-2P","1400-2","1405","1410P",
    "1410","1420P","1420","1430P","1430","1265#1","1265#2","1265#3","1265#4","1265#5",
    "1265#6","1265#7","1265#8","1265R","1430-2P#2","1430-2","1430-2P#1","2006U","2006L",
    "TT01","2010P1","2010P2","2010P3","2010","2020P","2020","2030P","2030","2040P","2040",
    "2050-1P","2050-1","2050-2P","2050-2","2055","2060","2070","2080","2090","2100","2110",
    "2120","2130-1P","2130-1","2130-2P","2130-2","2140P","2140","2150P","2150","2155","2160P",
    "2160","2170P","2170","2180P","2180","2185","2190P","2190","2196U","2196L","TT05","TT06#1",
    "TT06#2","TT06#3","TT06#4","TT06#5","TT06(2210)","TT07#1","TT07#2","TT07#3","TT07(2220)",
    "2230P","2230","TT08#0","TT08#1","TT08#2","2236U","2236L","TT08","2240P","2240","2250P",
    "2250","2255","2260","2270","2280","2290P","2290","2300P","2300","2310P","2310","2315",
    "2320P","2320","2330P","2340","2350P","2350","2355P","2355","2356U","2356L","TT09","2360",
    "2370P","2370","2375","2380P","2390","2410","2420P","2420","2430","2440","2450","2460P",
    "2460","2470P","2470","2475P","2475","2476U","2476L","TT10","2480P","2480","2490P","2490",
    "2500P","2500","2505P","2505#1","2505#2","2505B","2145#1","2145#2","2145#3","2145#4",
    "2145#5","2145#6","2145#7","2145#8","2145R"]

stasOperatingRate = ["1010","1020","1030","1035","1040","1050-1","1050-2","1060",
    "1080","1090","1095","1100","1110","1115","1120","1140","1150","1160","1165","1170","1180",
    "1195","1190","1210-3","1210-1","1210-2","1220","1230","1235","1240","1245","1250","1260",
    "1261","1270","1275","1280","1290","1295","1300","1310","1315","1320","1330","1340","1350A",
    "1350B","1355","1360","1368","1370","1380","1390","1400-1","1400-2","1405","1410","1420",
    "1430","1265R","2010","2020","2030","2040","2050-1","2050-2","2055","2060","2070","2080",
    "2090","2100","2110","2120","2130-1","2130-2","2140","2150","2155","2160","2170","2180",
    "2185","2190","2230","2240","2250","2255","2260","2270","2280","2290","2300","2310","2315",
    "2320","2340","2350","2355","2360","2370","2375","2390","2410","2420","2430","2440","2450",
    "2460","2470","2475","2480","2490","2500","2505B","2145R"]

def insertData(data,curDate,shift):
    cnx = pymysql.connect('127.0.0.1','root','12345678',"assembly" )
    cur = cnx.cursor()
    sqlfun = "insert into assembly.oprate (rate,date,shift)values('%s','%s','%s') "%(data,curDate,shift)
    cur.execute(sqlfun)
    cnx.commit()
    cur.close()
    cnx.close()

def updateData(data,curDate,shift):
    cnx = pymysql.connect('127.0.0.1','root','12345678',"assembly" )
    cur = cnx.cursor()
    sqlfun = "update assembly.oprate set rate='%s',time=now()  where date='%s' and shift='%s' "%(data,curDate,shift)
    cur.execute(sqlfun)
    cnx.commit()
    cur.close()
    cnx.close()
    

def getOpRate(curDate,shift):
    cnx = pymysql.connect('127.0.0.1','root','12345678',"assembly" )
    cur = cnx.cursor()
    sqlfun = "select * from assembly.oprate where date='%s' and shift='%s'"%(curDate,shift)
    cur.execute(sqlfun)
    data = cur.fetchall()
    cnx.commit()
    cur.close()
    cnx.close()
    return data

def andon_loadData(starttime,endtime):
    data = []
##    print(starttime,endtime)
    try:
        cnx = pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
        cur = cnx.cursor()
        sqlfun = "select layout_id from assembly.layout where layout_time<'%s' order by layout_id desc limit 1 "%starttime
        cur.execute(sqlfun)
        startid = cur.fetchall()[0][0]
        sqlfun = "select layout_id from assembly.layout where layout_time<'%s' order by layout_id desc limit 1"%endtime
        cur.execute(sqlfun)
        endid = cur.fetchall()[0][0]
        sqlfun = "select layout_stacom,layout_time from assembly.layout where layout_id between %d and %d order by layout_id desc "%(startid,endid)
        cur.execute(sqlfun)
        data = cur.fetchall()
        data = data[::-1]
        cnx.commit()
        cur.close()
        cnx.close()
    except:
        data = []
        print('Woooooo~~~ get andon data error...no data match the requirements.')

    return data


def andon_analyzeData(datatmp):
    operatingRate = []
    data = []
    for i in range(len(datatmp)):
        if datatmp[i][0][0].isdigit():
            data.append(datatmp[i])

    for j in range(261):
        sta = stations[j]
        orTmp = 0
        for i in range(len(data)):
            a = data[i][0][j]
            if a.isdigit():
                if int(a) in [5,6,7]:
                    orTmp += 1 
        if sta in stasOperatingRate:
            if len(data) == 0:
                operatingRate.append([sta,1])
            else:
                operatingRate.append([sta,1-orTmp/len(data)])
    operatingRate = sorted(operatingRate,key=lambda x:x[1])

    return operatingRate




def getAsWindow():
    hWnd = win32gui.FindWindow("Qt5150QWindowIcon",None)
    win32gui.ShowWindow(hWnd, win32con.SW_MAXIMIZE)
    left, top, right, bot = win32gui.GetWindowRect(hWnd)
    width = right - left
    height = bot - top
    hWndDC = win32gui.GetWindowDC(hWnd)
    mfcDC = win32ui.CreateDCFromHandle(hWndDC)
    saveDC = mfcDC.CreateCompatibleDC()
    saveBitMap = win32ui.CreateBitmap()
    saveBitMap.CreateCompatibleBitmap(mfcDC,width,height)

    saveDC.SelectObject(saveBitMap)

    saveDC.BitBlt((0,0), (width,height), mfcDC, (0, 0), win32con.SRCCOPY)

    result = windll.user32.PrintWindow(hWnd,saveDC.GetSafeHdc(),0)
    print(result)
    win32gui.ShowWindow(hWnd, win32con.SW_MINIMIZE)


    signedIntsArray = saveBitMap.GetBitmapBits(True)
    win32gui.DeleteObject(saveBitMap.GetHandle())
    saveDC.DeleteDC()
    mfcDC.DeleteDC()
    win32gui.ReleaseDC(hWnd,hWndDC)

    image = np.frombuffer(signedIntsArray, dtype = 'uint8')
    image.shape = (height, width, 4)
    image = image[246:750,15:-20]
##    cv.cvtColor(image, cv.COLOR_BGRA2RGB)
    cv.imwrite("A.png",image)

    return result


def GET_OP1110A_DATA(starttime,sta):
    cnx = pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur = cnx.cursor()
    sqlfun = "select id from assembly.output_loopa where time<'%s' and station = '%s' order by id desc limit 1 "%(starttime,sta)
    cur.execute(sqlfun)
    startid = cur.fetchall()[0][0]
    sqlfun = "select eun,rejectpiececount from assembly.output_loopa where id>%d   and station = '%s' order by id desc "%(startid,sta)
    cur.execute(sqlfun)
    tmp = cur.fetchall()
    tmp = tmp[::-1]
    cnx.commit()
    cur.close()
    cnx.close()

    data = set()
    for i in range(len(tmp)-1):
        if tmp[i+1][1] > tmp[i][1]:
            data = data|{tmp[i][0][:-4]}
        
    return data


def GET_OP1445R_DATA(starttime,reSta,targetsta):
    cnx = pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur = cnx.cursor()
    sqlfun = "select id from assembly.output_loopa where time<'%s' and station = '%s' order by id desc limit 1 "%(starttime,reSta)
    cur.execute(sqlfun)
    startid = cur.fetchall()[0][0]
    sqlfun = "select eun,other from assembly.output_loopa where id>%d   and station = '%s' order by id desc "%(startid,reSta)
    cur.execute(sqlfun)
    tmp = cur.fetchall()
    tmp = tmp[::-1]
    cnx.commit()
    cur.close()
    cnx.close()

    targetsta = int(targetsta[:4])

    
    data = set()
    for i in range(len(tmp)-1):
        sta = json.loads(tmp[i][1])['reject']
        if targetsta in sta:
            data = data|{tmp[i][0][:-4]}
            
    return data

def FIND_ENGINE(eun):
    cnx = pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur = cnx.cursor()
    sqlfun = "select station,time from assembly.output_loopa where eun like '%%%s%%'  order by id desc limit 1"%(eun)
    cur.execute(sqlfun)
    data = cur.fetchall()[0]

    cnx.commit()
    cur.close()
    cnx.close()

    return data

def getlayout():
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = "select layout_stacom from assembly.layout order by layout_id desc limit 30"
    cur.execute(sqlfun)
    datatmp=cur.fetchall()
    cnx.commit()
    cur.close()
    cnx.close()
    data = []
    for i in range(30):
        data.append(datatmp[i][0])
    return data

def updateFtq(data,curDate,shift):
    cnx = pymysql.connect('127.0.0.1','root','12345678',"assembly" )
    cur = cnx.cursor()
    sqlfun = "update assembly.oprate set ftq='%s'  where date='%s' and shift='%s' "%(data,curDate,shift)
    cur.execute(sqlfun)
    cnx.commit()
    cur.close()
    cnx.close()



def getlFTQ(curShiftDate,shiftProductionTobeSent):
    global shiftHour
    stas = ['1250M','1260M','2475A','2370A','3070A','3090A']
    stations = ['1010M','2490M','1430M-1','2010M','1250M','1260M','2130A-1','2130A-2','1020A','1030A',
                '1050M','1055M','1060M','1080A','1090M','1100M','1110A','1120A','1126R','1140M','1160A',
                '1170M','1180M','1190A','1220M','1230M','1240A','1265R','1270M','1280M','1300M','1261M',
                '1275A','1290A','1310A','1320A','1330M','1340M','1350A-1','1350A-2','1355B','1360M',
                '1370A','1380M','1390M','1400A-1','1400A-2','1410M','1420M','2020M','2030M','2060M',
                '2070M','2080M','2090M','2110M','2140M','2170M','2180A','2190M','2230M','2360M','2390M',
                '2400M','2460M','2470M','2480M','2475A','1210S1','1210S3','3010M','3030S2','3040S',
                '3050M','3060A1','3070A','3080A','3090A','3100A','3105R','3110A','3120M','2250M','2320M',
                '2350A','2240M','2280M','2300M','2340M','1090M','2050A-1','2050A-2','2290M','2310M',
                '2475A','2145R','2370A',]
    
    
    def xxx(station):
        cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
        cur=cnx.cursor()
        sqlfun = "select station,goodpiececount,rejectpiececount,time from assembly.output_loopa where (goodpiececount>10 OR rejectpiececount>10)  and station='%s' order by id desc limit 1"%station
        cur.execute(sqlfun)
        data=cur.fetchall()[0]
        cnx.commit()
        cur.close()
        cnx.close()

        return data

    staFTQ = {}
    st = time.time()
    ftq = []
    curDate = datetime.datetime.now().strftime("%Y-%m-%d")    
    for sta in stations:
        data = xxx(sta)
        tarTime = data[3]
##        print(tarTime)
        tarDate = tarTime.strftime("%Y-%m-%d")
        tarHour = int(tarTime.strftime("%H"))
        if tarHour >= 8 and tarHour < shiftHour:
            tarShift = '白'
        else:
            tarShift = '中'
        if tarDate == curDate and tarShift == shiftProductionTobeSent:
            staFTQ[data[0]] = {}
            staFTQ[data[0]]['good'] = data[1]
            staFTQ[data[0]]['reject'] = data[2]
            try:
                staFTQ[data[0]]['ftq'] = data[1]/(data[1]+data[2])
            except:
                staFTQ[data[0]]['ftq'] = 'NA'
        else:
            staFTQ[data[0]] = {}
            staFTQ[data[0]]['good'] = 0
            staFTQ[data[0]]['reject'] = 0
            staFTQ[data[0]]['ftq'] = 1

    ftqJson = json.dumps(staFTQ)

    updateFtq(ftqJson,curShiftDate,shiftProductionTobeSent)

    staFTQStr = "&&&&&&&&&&&&&&&&&&\n&&&&&&                 &&&&&&\n&&&&&& FTQ信息 &&&&&&\n&&&&&&                 &&&&&&\n&&&&&&&&&&&&&&&&&&\n\n班次：%s %s\n"%(curShiftDate,shiftProductionTobeSent)

    for x in staFTQ:
        if x in stas:
            ftqStr = str(round(staFTQ[x]['ftq']*100,2)) + '%'
            text = "工位：%s\n合格数：%d\n不合格数：%d\nFTQ：%s\n\n"%('OP'+x,staFTQ[x]['good'],staFTQ[x]['reject'],ftqStr)
            staFTQStr = staFTQStr + text

    return staFTQStr

def getShiftHour():
    global shiftHour
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = "SELECT shift,restday,shifts,jph,jph2,single_shift_starttime,single_shift_dur FROM assembly.shift_hour order by id desc limit 1"
    cur.execute(sqlfun)
    shiftHour,restDay,shifts,jph,jph2,single_shift_starttime,single_shift_dur = cur.fetchall()[0]
    if len(restDay) == 0:
        restDays = [9]
    else:
        restDays = [int(x) for x in restDay]
    cnx.commit()
    cur.close()
    cnx.close()
    data = []

    if int(shiftHour) == shiftHour:
        shiftHour = int(shiftHour)
    
    # shiftHour = 19.5
    return shiftHour,restDays,shifts,jph,jph2,single_shift_starttime,single_shift_dur

def check(layout,start,end,upper = 0,lower = 0):
    full = 0
    empty = 0
    flag = [False,False]
    for i in range(len(layout)):
        a = layout[i][start:end]
        a = [int(x) for x in a]
        bb = 0
        for b in a:
            if b in [1,2,6]:
                bb += 1
        if bb >= upper and upper != 0:
            full += 1
        if bb <= lower and lower != 0:
            empty += 1
    if full >= 25:
        flag[0] = True
    if empty >= 25:
        flag[1] = True
##    print(full,empty)
##    print(flag)
    return flag

def getAccessToken_alert():
    Secret = "bE-sWvYjmRY6fEyWS-lXnWUMpsBye4oXXRE95W0Uw_g"
    corpid = 'ww62a41290cf9fa91d'
    url = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid={}&corpsecret={}'
    getr = requests.get(url=url.format(corpid,Secret))
    access_token = getr.json().get('access_token')

    return access_token

def getAccessToken_FTQ():
    Secret = "HXvZYVwGw4uw-fYUlodlZ3XT1Q7Knqbj_NAT-WxG5b0"
    corpid = 'ww62a41290cf9fa91d'
    url = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid={}&corpsecret={}'
    getr = requests.get(url=url.format(corpid,Secret))
    access_token = getr.json().get('access_token')

    return access_token

def getAccessToken_OP1100M():
    Secret = "3BWjQiuIwqjTa7xVqw9nzGQ_Ig6pUezNJwXXJNGKPP8"
    corpid = 'ww62a41290cf9fa91d'
    url = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid={}&corpsecret={}'
    getr = requests.get(url=url.format(corpid,Secret))
    access_token = getr.json().get('access_token')

    return access_token


def uploadimage(filename, access_token):
    
    from requests_toolbelt import MultipartEncoder
    
    post_file_url = f"https://qyapi.weixin.qq.com/cgi-bin/media/upload?access_token={access_token}&type=file"

    m = MultipartEncoder(
        fields={filename: ('file', open(filename, 'rb'), 'text/plain')},
    )
    r = requests.post(url=post_file_url, data=m, headers={'Content-Type': m.content_type})
    
    return r.json()['media_id']

def makeLayoutPic(infoo):
    font=cv.FONT_HERSHEY_SIMPLEX
    def getLayout():
        cnx = pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
        cur = cnx.cursor()
        sqlfun = "select layout_stacom from assembly.layout order by layout_id desc limit 1"
        cur.execute(sqlfun)
        data = cur.fetchall()
        cnx.commit()
        cur.close()
        cnx.close()
        return data
    def change(p,sta):
        if i >= 128:
            x = int(p[0] * step + ori[0])
            y = int(p[1] * step + ori[1]) - 12
        else:
            x = int(p[0] * step + ori[0])
            y = int(p[1] * step + ori[1])
        default[y:y+step,x:x+step] = imgs[sta]
    addr = [[0,1],[0,2],[1,2],[2,2],[3,2],[4,2],[5,2],[6,2],[7,2],[8,2],[9,2],[10,2],[11,2],[12,2],[13,2],[14,2],[15,2],[16,2],[17,2],[18,2],[19,2],[20,2],[21,2],[22,2],[23,2],[24,0],[24,1],[24,2],[25,2],[26,2],[27,2],[28,2],[29,2],[30,2],[31,2],[32,0],[32,1],[32,2],[33,2],[34,2],[35,2],[36,2],[37,2],[38,2],[39,2],[40,2],[41,2],[42,2],[43,2],[44,2],[45,2],[46,2],[47,0],[47,1],[47,2],[48,2],[49,2],[50,2],[51,2],[52,2],[53,2],[53,3],[53,4],[53,5],[53,6],[52,6],[51,6],[50,6],[49,6],[47.5,6],[46.5,6],[45,6],[44,6],[43,6],[42,6],[41,6],[40,6],[39,6],[38,6],[37,6],[36,6],[35,6],[34,6],[33,6],[32,6],[31,6],[30,6],[29,6],[28,6],[27,6],[26,6],[25,6],[24,6],[23,6],[22,6],[21,6],[20,6],[19,6],[18,6],[17,6],[16,6],[15,6],[14,6],[13,6],[12,6],[11,6],[10,6],[9,6],[8,6],[7,6],[6,6],[5,6],[4,6],[3,6],[2,6],[1,6],[0,6],[0,4.5],[0,3.5],[45,9],[46,9],[47,9],[48,9],[49,9],[50,9],[51,9],[52,9],[53,9],[53,15],[53,16],[53,17],[53,20],[53,19],[53,18],[52,18],[51,18],[50,18],[49,18],[48,18],[47,18],[46,18],[45,18],[44,18],[43,18],[42,18],[41,18],[40,18],[39,18],[38,18],[37,18],[36,18],[35,18],[34,18],[33,18],[32,18],[31,18],[30,18],[29,18],[28,18],[27,18],[26,18],[25,18],[24,18],[23,18],[22,18],[21,18],[20,18],[19,18],[18,18],[17,18],[16,18],[15,18],[14,18],[13,18],[12,20],[12,19],[12,18],[11,18],[10,18],[9,18],[8,18],[7,18],[6,18],[5,18],[4,18],[3,18],[2,18],[1,18],[0,18],[0,17],[0,16],[0,15],[0,12],[0,13],[0,14],[1,14],[2,14],[3,14],[4,14],[5,14],[6,14],[7,14],[8,14],[9,14],[10,14],[11,14],[12,14],[13,14],[14,14],[15,14],[16,14],[17,14],[18,14],[19,14],[20,14],[21,14],[22,14],[23,14],[24,12],[24,13],[24,14],[25,14],[26,14],[27,14],[28,14],[29,14],[30,14],[31,14],[32,14],[33,14],[34,14],[35,14],[36,14],[37,14],[38,14],[39,14],[40,14],[41,14],[42,14],[43,12],[43,13],[43,14],[44,14],[45,14],[46,14],[47,14],[48,14],[49,14],[50,14],[51,14],[52,14],[53,14],[12,11],[13,11],[14,11],[15,11],[16,11],[17,11],[18,11],[19,11],[20,11]]
    step = 23
    ori = [16,7]
    d = cv.imread('default.png')
    default = copy.deepcopy(d)
    imgs = []
    for i in range(8):
        img = cv.imread('imagePMC/'+str(i)+'.png')
        img = cv.resize(img,(step,step))
        imgs.append(img)
    img = cv.imread('imagePMC/!.png')
    img = cv.resize(img,(step,step))
    imgs.append(img)
    img = cv.imread('imagePMC/@.png')
    img = cv.resize(img,(step,step))
    imgs.append(img)
    
    layout = getLayout()[0]
    default = copy.deepcopy(d)
    
    for i in range(261):
        if layout[0][i].isdigit():
            sta = int(layout[0][i])
        elif layout[0][i] == '!':
            sta = 8
        elif layout[0][i] == '@':
            sta = 9
        if sta > 0:
            change(addr[i],sta)
    
    cv.rectangle(default,(130,0),(490,40),(240,176,0),-1,lineType=cv.LINE_AA)
    cv.putText(default,str(datetime.datetime.now()),(150,15),font, 0.5, (255,255,255),1,lineType=cv.LINE_AA)
##    info = "%dJPH  TARGET:%d  CUR:%d  DIFF:%d"%()
    cv.putText(default,infoo,(150,35),font, 0.5, (255,255,255),1,lineType=cv.LINE_AA)
##    cv.imshow('xxx',default)
##    cv.waitKey(10)
    cv.imwrite('layout.png',default)

##def sendImage(access_token,person,path,infoo):
##    ret = 1
##    try:
####        ret = getAsWindow()
##        makeLayoutPic(infoo)
##        print('ret:',ret)
##    except Exception as e:
##        print(e)
##
##    if ret == 1:
##        image_obj = uploadimage(path, access_token)
##        return image_obj
        
    #     data = {
    # ##       "touser" : person,   
    #        "toparty" : person,#"PartyID1|PartyID2",   
    #        "msgtype" : "image",
    #        "agentid" : 1000003,
    #        "image" : {'media_id':image_obj},
    #        "safe":0
    #     }

    #     r = requests.post(url="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={}".format(access_token),
    #                   data=json.dumps(data))
    # ##    print(r.json())
    #     return  r.json()['errmsg'],ret

# def starvBlockAlert(access_token_alert,person,text):
#     curtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

#     data = {
# ##       "touser" : person,   
#        "toparty" : person,#"PartyID1|PartyID2",   
#        "msgtype" : "text",
#        "agentid" : 1000003,
#        "text" : {
#            "content" : text
#        },
#        "safe":0
#     }
#     r = requests.post(url="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={}".format(access_token_alert),
#                       data=json.dumps(data))

#     print(r.json())

#     return  r.json()['errmsg']

def sendImage(access_token,path):
    ret = 1
    try:
        makeLayoutPic(infoo)
        print('ret:',ret)
    except Exception as e:
        print(e)

    if ret == 1:
        image_obj = uploadimage(path, access_token)
        return image_obj

def starvBlockAlert(access_token_alert,person,text,digest,title):
##    person = 'zhangsan'
    imageid = sendImage(access_token_alert,'layout.png')
    imageurl = "https://qyapi.weixin.qq.com/cgi-bin/media/get?access_token=%s&media_id=%s"%(access_token_alert,imageid)
    text = '<img src="%s" alt="img"><br><br>'%imageurl + text
    data = {
##               "touser" : person,
               "toparty" : person,
               "msgtype" : "mpnews",
               "agentid" : 1000003,
               "mpnews" : {
                   "articles":[
                       {
                           "title": title, 
                           "thumb_media_id": imageid,
                           "author": "CQPT-AS",
                           "content": text,
                           "digest": digest,
                        }
                   ]
               },
               "safe":0,
               "enable_id_trans": 1,
               "enable_duplicate_check": 0,
               "duplicate_check_interval": 1800
            }
    r = requests.post(url="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={}".format(access_token_alert),
                    data=json.dumps(data))

    return  r.json()['errmsg']

def getCurrentFtqDict():
    cnx = pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur = cnx.cursor()
    sqlfun = "select ftq from assembly.oprate order by id desc limit 1 "
    cur.execute(sqlfun)
    data = cur.fetchall()[0][0]
    cnx.commit()
    cur.close()
    cnx.close()
    data = json.loads(data)

    return data

def makeFtqTable():
    ftq = getCurrentFtqDict()
    htmlStr = ''
    head = '''
            <table  border='-1'>
                <tr bgcolor='blue'>
                    <th>工位</th>
                    <th>合格计数</th>
                    <th>不合格计数</th>
                    <th>FTQ</th>
                </tr>
                %s
            </table>
            '''
    body = ''
    for key in ftq:
        if key in ['1080A','1110A','1250M','1260M','2475A','3070A','3090A','2370A','1265R','2145R','2320M','2350A']:
            ff = str(round(ftq[key]['ftq']*100,2))+'%'
            tmp = '''
                    <tr align='center'>
                        <td>%s</td>
                        <td>%d</td>
                        <td>%d</td>
                        <td>%s</td>
                    </tr>
                    '''%(key,ftq[key]['good'],ftq[key]['reject'],ff)
            tmp.replace('2320M','2320A')
            body = body + tmp
    htmlStr = head%body
    
    return htmlStr

def makeloopStrTable(loopaCounts,loopbCounts,shiftHour,single_shift_starttime,single_shift_dur):
    length = len(loopaCounts)
    curHour = int(datetime.datetime.now().strftime("%H"))
    curMinute = int(datetime.datetime.now().strftime("%M"))
    print("xxxx:",shiftHour,single_shift_starttime,single_shift_dur)

    if single_shift_dur is not None:
        headHours = [h%24 for h in range(int(single_shift_dur),int(single_shift_dur+single_shift_dur))]
    else:
        if int(shiftHour) == shiftHour:
            if curHour >=8 and curHour < shiftHour:
                headHours = [h for h in range(8,8+length)]
            else:
                headHours = [h%24 for h in range(shiftHour,shiftHour+length)]
        else:
            if (curHour + curMinute/60) <= shiftHour:
                headHours = [h for h in range(8,8+length)]
            else:
                headHours = [h%24 for h in range(int(shiftHour),int(shiftHour)+length)]

    
    htmlStr = ''
    hourStr = ''
    for h in headHours:
        hourStr = hourStr + '<th>%d</th>'%h
    head = '''
            <table  border='-1'>
                <tr bgcolor='blue'>
                    <th></th>
                    %s
                </tr>
            '''%hourStr
            
    head = head + '%s</table>'
    bodyloopa = '''
            <tr align='center'>
                <td>LOOPA</td>
                %s
            </tr>
            '''
    bodyloopb = '''
            <tr align='center'>
                <td>LOOPB</td>
                %s
            </tr>
            '''
    tmpa = ''
    tmpb = ''
    for a,b in zip(loopaCounts,loopbCounts):
        tmpa = tmpa + '<td>%d</td>'%a
        tmpb = tmpb + '<td>%d</td>'%b
    
    bodyloopa = bodyloopa%tmpa
    bodyloopb = bodyloopb%tmpb
    body = bodyloopa + bodyloopb

    htmlStr = head%body
    
    return htmlStr

# def makeCountsTable():
#     htmlStr = ''
#     head = '''
#             <table  border='-1'>
#                 <tr bgcolor='blue'>
#                     <th>工位</th>
#                     <th>合格计数</th>
#                     <th>不合格计数</th>
#                     <th>FTQ</th>
#                 </tr>
#                 %s
#             </table>
#             '''
#     body = ''
#     for key in ftq:
#         if key in ['1110A','1250M','1260M','2475A','3070A','3090A','2370A','1265R','2145R']:
#             ff = str(round(ftq[key]['ftq']*100,2))+'%'
#             tmp = '''
#                     <tr align='center'>
#                         <td>%s</td>
#                         <td>%d</td>
#                         <td>%d</td>
#                         <td>%s</td>
#                     </tr>
#                     '''%(key,ftq[key]['good'],ftq[key]['reject'],ff)
#             body = body + tmp
#     htmlStr = head%body
    
#     return htmlStr

def sendProduction(access_token_FTQ,person,text):
    curtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    data = {
##       "touser" : person,   
       "toparty" : person,#"PartyID1|PartyID2",   
       "msgtype" : "text",
       "agentid" : 1000005,
       "text" : {
           "content" : text
       },
       "safe":0
    }
    r = requests.post(url="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={}".format(access_token_FTQ),
                      data=json.dumps(data))

    print(r.json())

    return  r.json()['errmsg']

def hourCount(counts,shiftHour):
    perHourCount = []
    for i in range(ceil(shiftHour)-8):
        perHourCount.append(int(counts[2*i:2*(i+1)]))
    print(len(perHourCount),perHourCount)
    return perHourCount

def getHourCounts(shiftProductionTobeSent,curShiftDate,shiftHour,single_shift_starttime,single_shift_dur):
##    curShiftDate,curShift = '2020-08-18','中'
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    if single_shift_starttime > 0:
        sqlfun = "select * from assembly.fine_as_count where date_filter='%s' and `loop`='b' limit 1"%(curShiftDate)
        tmp = cur.fetchall()
        total = []
        for t in tmp:
            total += t[4:-1]
        counts = counts[int(single_shift_starttime - 8):int(single_shift_dur+single_shift_starttime)]
        # print(total)


    else:
        sqlfun = "select * from assembly.fine_as_count where date_filter='%s' and shift='%s' and `loop`='b' limit 1"%(curShiftDate,shiftProductionTobeSent)
        cur.execute(sqlfun)
        counts = cur.fetchall()[0][4:-1]
    ##    print('getcounts:',counts)
        counts = list(counts)[:ceil(shiftHour-8)]
    ##    print('getcounts:',counts)
    cnx.commit()
    cur.close()
    cnx.close()

    return counts


def getCountsDetails(shiftHour,curShiftDate):
    curDate = datetime.datetime.now().strftime("%Y-%m-%d")
    curHour = int(datetime.datetime.now().strftime("%H"))
    
    if curHour == int(shiftHour):
        starttime =  curDate + ' ' +'08:00:00'
        endtime = curShiftDate + ' ' + str(int(shiftHour)) + ':'+str(int(60*(shiftHour-int(shiftHour))))+':00'
    else:
        starttime = curShiftDate + ' ' + str(int(shiftHour)) + ':'+str(int(60*(shiftHour-int(shiftHour))))+':00'
        endtime = curDate+ ' ' +'08:00:00'

    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = "SELECT eun FROM (SELECT * FROM assembly.output_loopa where station='2490M'"\
        " order by id desc limit 3000) as e where e.time >'%s' and e.time < '%s'"%(starttime,endtime)
    cur.execute(sqlfun)
    eunsTmp = cur.fetchall()
    cnx.commit()
    cur.close()
    cnx.close()

    types = []

    for x in eunsTmp:
        types.append(x[0][3:5])

    typesSet = set(types)
    typeCount = {}

    for y in typesSet:
        typeCount[y] = types.count(y)

    typeCountStr = '('
    for item in typeCount:
        typeCountStr = typeCountStr + item + ':' + str(typeCount[item]) + ','
    typeCountStr = typeCountStr[:-1]+')'

    return typeCountStr,typeCount

def updateDailyCountData(curShiftDate,shiftProductionTobeSent,jph,sumCounts,countsDiff,typeCount,shiftHour):
    target = sumCounts - countsDiff
    total = sum([typeCount[x] for x in typeCount])
    diff = total - target
    if sumCounts > total:
        total = sumCounts
        diff = countsDiff
    data = {"total":total,"diff":diff,"jph":jph,"shifthour":shiftHour,"counts":typeCount}
    # data = {"total":sumCounts,"diff":countsDiff,"jph":jph,"shifthour":shiftHour,"counts":typeCount}
    data = json.dumps(data)

    cnx=pymysql.connect('127.0.0.1','root','12345678',"assembly" )
    cur=cnx.cursor()
    sqlfun = "UPDATE assembly.oprate SET COUNTS = '%s' WHERE shift='%s' and date='%s' "%(data,shiftProductionTobeSent,curShiftDate)
    cur.execute(sqlfun)
    cnx.commit()
    cur.close()
    cnx.close()


def getStaRepairDiff(curShift,curShiftDate,sta,reSta):
    curDate = datetime.datetime.now().strftime("%Y-%m-%d")
    curHour = int(datetime.datetime.now().strftime("%H"))

    banci = {1:'中班',2:'白班'}
    
    if curHour == int(shiftHour):
        starttime =  curDate + ' ' +'08:00:00'
        # endtime = curShiftDate + ' ' + str(int(shiftHour)) + ':'+str(int(60*(shiftHour-int(shiftHour))))+':00'
    else:
        starttime = curShiftDate + ' ' + str(int(shiftHour)) + ':'+str(int(60*(shiftHour-int(shiftHour))))+':00'
        # endtime = curDate+ ' ' +'08:00:00'

    OP1110ADATA = GET_OP1110A_DATA(starttime,sta)
    OP1445RDATA = GET_OP1445R_DATA(starttime,reSta,sta)
    different = OP1110ADATA - OP1445RDATA

    text = '%s %s\nOP%s不合格但未经过OP%s:\n'%(curShiftDate,banci[int(curShift)],sta,reSta)
    if len(different) != 0:
        for x in different:
            station,time = FIND_ENGINE(x)
            text = text + x +' '+ station +' ' + str(time) +'\n'
        print(text)
    else:
        if len(OP1110ADATA) != 0:
            text = '%s %s:\nOP%s暂未发现问题。\n不合格清单:\n'%(curShiftDate,banci[int(curShift)],sta)
            ttttt = ',\n'.join(OP1110ADATA)
            text = text + ttttt
        else:
            text = '%s %s:\nOP%s暂未发现问题。\n'%(curShiftDate,banci[int(curShift)],sta)

        print('OP1110A暂时没发现问题。。。')

    return text
    


def sendShiftProduction(curShift,curShiftDate,shiftHour,jph,single_shift_starttime,shifts,single_shift_dur):
    global access_token_FTQ
    flag = ''
    targets = {
        40:{8:288,9:328,10:362,11:396,11.5:416,12:436},
        50:{8:359,9:409,10:451,11:493,11.5:518,12:543},
        67:{8:480,9:547,10:603,11:658,11.5:692,12:725},
        70:{8:503,9:573,10:632,11:688,10.5:636,11.5:723,12:758},
        72:{8:516,9:588,10:648,11:708,11.5:744,12:775},
        30:{8:215,9:245,10:270,11:295,11.5:310,12:325},
        60:{8:430,9:490,10:540,11:590,11.5:620,12:650},
        65:{8:468,9:530,10:588,11:643,11.5:676,12:708},
        66:{8:473,9:539,10:594,11:649,11.5:682,12:715},
        63:{8:453,9:514,10:569,11:622,11.5:654,12:685},
        35:{8:253,9:288,10:318,11:348,11.5:366,12:398},
        32:{8:230,9:262,10:290,11:316,11.5:332,12:363},
        45:{8:324,9:369,10:407,11:445,11.5:468,12:490},
        62:{8:443,9:505,10:556,11:607,11.5:638,12:669},
        55:{8:395,9:450,10:496,11:542,11.5:570,12:597},
        56:{8:402,9:458,10:505,11:552,11.5:580,12:608},
        
        }
    try:
        if single_shift_starttime is None:
            target = targets[jph][shiftHour - 8]
        else:
            target = targets[jph][single_shift_dur]
    except:
        hourCountTargets,_ = getHourTargetRests(jph)
        if single_shift_starttime is None:
            target = sum(hourCountTargets[shiftHour - 8]) / 2
        else:
            target = sum(hourCountTargets[single_shift_dur])/2
        
    if curShift == 2:
        shiftProductionTobeSent = '白'
    else:
        shiftProductionTobeSent = '中'

##    print('shiftProductionTobeSent',shiftProductionTobeSent)
    try:
        counts = getHourCounts(shiftProductionTobeSent,curShiftDate,shiftHour,single_shift_starttime,single_shift_dur)
    except Exception as e:
        print(e)
        counts = []
    print(counts)

    stas = ['1110A','1190A','1080A','2320M']
    reStas = ['1445R','1445R','1445R','2515R']
    SRDtext = []

    try:
        typeCountStr,typeCount = getCountsDetails(shiftHour,curShiftDate)
        for sta,reSta in zip(stas,reStas):
            SRDtext.append(getStaRepairDiff(curShift,curShiftDate,sta,reSta))
    except Exception as e:
        print(e)
        typeCountStr = ''

    if len(counts) == 0:
        pass
    else:
        sumCounts = sum(counts)
        countsStr = ','.join([str(x) for x in counts])

        countsDiff = sumCounts - target
        if countsDiff > 0:
            t = '超产%d台...'%countsDiff
        elif countsDiff < 0:
            t = '欠产%d台...'%abs(countsDiff)
        else:
            t = '未欠产....'

        text = "&&&&&&&&&&&&&&&&&&\n&&&&&&                 &&&&&&\n&&&&&& 产量信息 &&&&&&\n&&&&&&                 &&&&&&\n&&&&&&&&&&&&&&&&&&\n\n班次：%s %s\n\n(%sJPH)\n总产量：%d%s\n小时产量：%s\n\n%s"%(curShiftDate,shiftProductionTobeSent,str(jph),sumCounts,typeCountStr,countsStr,t)
        try:
            updateDailyCountData(curShiftDate,shiftProductionTobeSent,jph,sumCounts,countsDiff,typeCount,shiftHour)
        except:
            pass
##        person = 'ZhangSan|LiuXin|Lce|JiangChunXi|TanShangDi'
        person = '13'
        msg = sendProduction(access_token_FTQ,person,text)
        
        if msg != 'ok':
            flag = False
            access_token_FTQ = getAccessToken_FTQ()
            msg = sendProduction(access_token_FTQ,person,text)
            if msg == 'ok':
                flag = True
        else:
            flag = True

        staFTQStr = getlFTQ(curShiftDate,shiftProductionTobeSent)
        msg = sendProduction(access_token_FTQ,person,staFTQStr)
        
        if SRDtext == None:
            print('OP1110A暂时没发现问题')
        else:
            for sss in SRDtext:
                msg = sendProduction(access_token_FTQ,person,sss)
        
        print('flag:',flag)
        print(text)
    return flag

def getDiffs():
    try:
        cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
        cur=cnx.cursor()
        
        sqlfun = "select goodpiececount from assembly.output_loopa where station='1100M' order by id desc limit 1"
        cur.execute(sqlfun)
        goodcount_1100 = cur.fetchall()[0][0]

        sqlfun = "select goodpiececount from assembly.output_loopa where station='1180M' order by id desc limit 1"
        cur.execute(sqlfun)
        goodcount_1180 = cur.fetchall()[0][0]

        sqlfun = "select goodpiececount from assembly.output_loopa where station='1300M' order by id desc limit 1"
        cur.execute(sqlfun)
        goodcount_1300 = cur.fetchall()[0][0]
        
        cnx.commit()
        cur.close()
        cnx.close()
    except Exception as e:
        goodcount_1100 = 0
        goodcount_1180 = 0
        goodcount_1300 = 0
        print(e)

    return goodcount_1100,goodcount_1180,goodcount_1300

def insertCountsDiff(countsDiffA,countsDiffB,countsDiff_1100,countsDiff_1180,countsDiff_1300,rest):
    try:
        cnx=pymysql.connect('127.0.0.1','root','12345678',"assembly" )
        cur=cnx.cursor()
        sqlfun = "insert into assembly.production_diff(diff_1430,diff_2490,diff_1100,diff_1180,diff_1300,rest)values(%d,%d,%d,%d,%d,%d);"%(countsDiffA,countsDiffB,countsDiff_1100,countsDiff_1180,countsDiff_1300,rest)
        cur.execute(sqlfun)
        cnx.commit()
        cur.close()
        cnx.close()
    except Exception as e:
        print(e)

def run():
    while 1:
        shiftHour,_,shifts,jph,jph2,single_shift_starttime,single_shift_dur = getShiftHour()

        curtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        curDate = datetime.datetime.now().strftime("%Y-%m-%d")
        yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
        curHour = int(datetime.datetime.now().strftime("%H"))
        curMinute = int(datetime.datetime.now().strftime("%M"))
        curSecond = int(datetime.datetime.now().strftime("%S"))
        curWeekDay = datetime.datetime.now().weekday()

        if curHour >=8 and curHour < shiftHour:
            shift = '白'
            curShiftDate = curDate
            starttime = curShiftDate + ' 08:00:00'
        else:
            if curHour >= shiftHour:
                curShiftDate = curDate
            else:
                curShiftDate = yesterday
            shift = '中'
            starttime = curShiftDate + ' ' + str(int(shiftHour)) + ':'+str(60*int(shiftHour-int(shiftHour)))+':00'

        
        staFTQStr = getlFTQ(curShiftDate,shift)
        
##        starttime = '2020-09-23 08:00:00'
        starttime = curShiftDate + ' 08:00:00'
        endtime = curtime
        
        
        data = andon_loadData(starttime,endtime)
        operatingRate = andon_analyzeData(data)
        
        d = json.dumps(operatingRate)

        if curHour >= ((shiftHour-8)*2+8-24) and curHour < 8:
            pass
        else:
            print('curShiftDate:',curShiftDate,' , shift:',shift)
            print('starttime:',starttime,' , endtime:',endtime)
            ddd = getOpRate(curShiftDate,shift)
            if len(ddd) == 0:
                insertData(d,curShiftDate,shift)
            else:
                updateData(d,curShiftDate,shift)
        
        time.sleep(20)

def sendOP1100MAlert():
    def getInfo():
        try:
            cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
            cur=cnx.cursor()
            sqlfun = "SELECT * FROM assembly.ep_visual where EP_VISUAL_sta='op1100' order by ep_visual_id desc limit 1"
            cur.execute(sqlfun)
            data = cur.fetchall()[0]
            cnx.commit()
            cur.close()
            cnx.close()
        except Exception as e:
            print(e)
            data = []
        return data

    def sendAlert(access_token_OP1100M,text):
        data = {
        "toparty" : "17", 
        "msgtype" : "text",
        "agentid" : 1000006,
        "text" : {
            "content" : text
        },
        "safe":0
        }
        r = requests.post(url="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={}".format(access_token_OP1100M),
                        data=json.dumps(data))

        print(r.json())

        return  r.json()['errmsg']

    access_token_OP1100M = getAccessToken_OP1100M()
    lastEUN = ''

    while 1:
        data = getInfo()
        try:
            if data != []:
                if data[3] == 1:
                    print('@@@ station:',data[1],' status:1',' EUN:',data[2],' TIME:',data[4])

                elif data[3] == 0:
                    print('@@@ station:',data[1],' status:0',' EUN:',data[2],' TIME:',data[4])
                    if lastEUN != data[2]:
##                        print('lastEun = data[2]',lastEun == data[2])
                        lastEUN = data[2]
##                        print('lastEun = data[2]',lastEun == data[2])
                        text = "###  OP1100M防错报警  ###\n\n"+\
                                "时间："+str(data[4])+'\n'+\
                                    "EUN:"+data[2]+'\n'

                        print(text)                
                        msg = sendAlert(access_token_OP1100M,text)
##                        print(msg,msg=='ok',lastEun,data[2])
                        if msg != 'ok':
                            access_token_OP1100M = getAccessToken_OP1100M()
                            msg = sendAlert(access_token_OP1100M,text)
                        time.sleep(20)
                    else:
                        print('lastEUN == curEUN')
                        
        except Exception as e:
            print(e)
        time.sleep(10)

def getJPHTarget():
    try:
        cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
        cur=cnx.cursor()
        sqlfun = "SELECT JPH FROM assembly.shift_hour order by id desc limit 1"
        cur.execute(sqlfun)
        jph = cur.fetchall()[0][0]
        cnx.commit()
        cur.close()
        cnx.close()
    except Exception as e:
        print(e)
        jph = 67
    return jph

def getHourTargetRests(jph):
    cnx=pymysql.connect('127.0.0.1','root','12345678',"assembly" )
    cur=cnx.cursor()
    sqlfun = 'select target,rest from assembly.shift_target_rest where jph=%d limit 1'%jph
    cur.execute(sqlfun)
    y = cur.fetchall()
    
    if len(y) == 0:
        jph_rest_10 = str(ceil(jph * 50 / 60))
        jph_rest_30 = str(ceil(jph / 2))
        sqlfun = 'select target,rest from assembly.shift_target_rest where jph=60 limit 1'
        cur.execute(sqlfun)
        jph60 = cur.fetchall()[0]
        # print('######',jph60)
        
        newHT = jph60[0].replace("60",str(jph)).replace("30",jph_rest_30).replace("50",jph_rest_10)
        y = [newHT,jph60[1]]
        
        sqlfun = "INSERT INTO `assembly`.`shift_target_rest` (`jph`, `target`, `rest`) VALUES ('%s', '%s', '%s');"%(str(jph),newHT,jph60[1])
        cur.execute(sqlfun)

    else:
        y = y[0]

    cnx.commit()
    cur.close()
    cnx.close()
    
    
        

    rests= eval(json.loads(y[1]))
    hourTargets= eval(json.loads(y[0]))
    
    return hourTargets,rests

def getKeyParaLimits():
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = 'select key_para from assembly.key_para order by id limit 1'
    cur.execute(sqlfun)
    y = json.loads(cur.fetchall()[0][0])
    cnx.commit()
    cur.close()
    cnx.close()

    

    return y

def getStaParaInfo(sta,para):
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = "select * from assembly.key_para where station='%s' and datatype='%s' order by id desc limit 1"%(sta,para)
    cur.execute(sqlfun)
    data = cur.fetchall()[0]
    cnx.commit()
    cur.close()
    cnx.close()

    return data

def sendKeyParaAlert(access_token,text,per):
    data = {
    "toparty" : per, 
    "msgtype" : "text",
    "agentid" : 1000006,
    "text" : {
        "content" : text
    },
    "safe":0
    }
    r = requests.post(url="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={}".format(access_token),
                    data=json.dumps(data))

    print(r.json())

    return  r.json()['errmsg']

def getStaFTQ(sta):
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = "select count_good,count_reject,ftq from assembly.fine_as where station='%s'"%sta
    cur.execute(sqlfun)
    tmp = cur.fetchall()[0]
    cnx.commit()
    cur.close()
    cnx.close()

    good = tmp[0]
    reject = tmp[1]+1
##    ftq = str(round(tmp[2]*100,2))+'%'
    sum_ = good + reject
    ftq = str(round((good/sum_)*100,2))+'%'
##    ftq = str(round((reject+1)/(good+reject+1)*100,2))+'%'
    
    staFTQ = '合格:'+str(good)+'台\n不合格:'+str(reject)+'台\nFTQ:'+ftq

    return staFTQ


def keyParaAlert():
    keyParaLimits = getKeyParaLimits()
    limits = keyParaLimits['N15']
    types = {
        'N12':['RZ','AZ','BW','SM','HA','CV','NG','NH','CE','CK'],
        'N15':['RA','CS','DF','RE','RC','RF','SA','SB']
    }

    eunsAlert = copy.deepcopy(keyParaLimits)
##    print(eunsAlert)
    
    for t in eunsAlert:
        for sta in eunsAlert[t]:
            for para in eunsAlert[t][sta]:
                for args in eunsAlert[t][sta][para]:
                    eunsAlert[t][sta][para][args] = ''

    print(eunsAlert)

    access_token_keyPara = getAccessToken_OP1100M()

    count = 0

    paraTranslations = {
        'torque':'扭矩','torque1':'扭矩1','torque2':'扭矩2','torque3':'扭矩3','torque4':'扭矩4',
        'torque5':'扭矩5','torque6':'扭矩6','torque7':'扭矩7','torque8':'扭矩8','torque9':'扭矩9',
        'torque10':'扭矩10','angle':'角度','angle1':'角度1','angle2':'角度2','angle3':'角度3','angle4':'角度4',
        'angle5':'角度5','angle6':'角度6','angle7':'角度7','angle8':'角度8','angle9':'角度9','angle10':'角度10',
        'dis':'距离','dis1':'1缸','dis2':'2缸','dis3':'3缸','dis4':'4缸','bear_check':'轴瓦漏装检测',
        'low':'最小值','high':'最大值','mean':'平均值','peak':'峰值','TTT':'TTT','max1':'最大值1','max2':'最大值2',
        'max3':'最大值3','max4':'最大值4','min1':'最小值1','min2':'最小值2','min3':'最小值3','min4':'最小值4',
    }

    per = '16'

    while 1:
        for t in keyParaLimits:
            for sta in keyParaLimits[t]:
                for para in keyParaLimits[t][sta]:
                    data = getStaParaInfo(sta,para)
                    if data[3][3:5] in types['N15']:
                        limits = keyParaLimits['N15']
                        typeTmp = 'N15'
                    else:
                        limits = keyParaLimits['N12']
                        typeTmp = 'N12'
                    if typeTmp == t:
                        # testDataTmp = json.loads(data[4])
                        testData = json.loads(data[4])

                        # testData = copy.deepcopy(testDataTmp)

                        # for k in testDataTmp:
                        #     if  'min' in k or 'max' in k:
                        #         del testData[k]
                        

                        text = "####  关键参数报警  ####\n\n"+\
                                "工位：OP"+sta+'\n'+\
                                    "时间："+str(data[5])+'\n'+\
                                        "EUN:"+data[3]+'\n\n'
                        staFTQ = ''
                        if sta in ['2050A-2']:
                            per = '18'
                        else:
                            per = '17'
                        ffff = False
                        # for k in testData:
                        for k in limits[sta][para]:
                            flag = False
                            if 'max' in k or 'min' in k:
                                pass
                            else:
                                if testData[k] < limits[sta][para][k][0] or testData[k] > limits[sta][para][k][1]:
                                    flag = True
                                    ffff = True
                                try:
                                    if flag == True and data[3] != eunsAlert[t][sta][para][k]:
                                        print('XXXXXXXXXXXXXXXXXXXXX',t,sta,para,k,testData[k],limits[sta][para][k],data[3])
                                        
                                        text = text + \
                                            "参数："+paraTranslations[para]+'-'+paraTranslations[k]+'\n'+\
                                                '限定值：'+str(limits[sta][para][k][0])+'~'+str(limits[sta][para][k][1])+'\n'+\
                                                    '实际值：'+str(testData[k])+'\n\n'
                                except Exception as e:
                                    print(e)
                                    print(t,sta,para,k)

                                try:
                                    staFTQ = getStaFTQ(sta)
                                except Exception as e:
                                    staFTQ = ''
                                    print(sta,e)
                                
                        text = text + staFTQ
                        
                        if ffff == True and data[3] != eunsAlert[t][sta][para][k]:
                            eunsAlert[t][sta][para][k] = data[3]
                            print(text)
                            msg = sendKeyParaAlert(access_token_keyPara,text,per)
                            if msg != 'ok':
                                access_token_keyPara = getAccessToken_OP1100M()
                                msg = sendKeyParaAlert(access_token_keyPara,text,per)
                        
##        print('########@@@@@@@@@@@@############\n',eunsAlert,'\n')
        print('ffff == True and data[3] != eunsAlert[t][sta][para][k] :',ffff == True)
        if count % 5 == 0:
            keyParaLimits = getKeyParaLimits()
        count += 1
        time.sleep(10)

def getStaInfo():
    cnx=pymysql.connect('127.0.0.1','CHUAN','password',"assembly" )
    cur=cnx.cursor()
    sqlfun = "select station,eun from assembly.fine_as "
    cur.execute(sqlfun)
    tmp = cur.fetchall()
    cnx.commit()
    cur.close()
    cnx.close()
    dataTmp = []
    for i in range(len(tmp)):
        if tmp[i][1] != None and tmp[i][0][0] != '3' and tmp[i][0][-1] != 'R' and tmp[i][0] != '1008B':
            dataTmp.append([tmp[i][0],tmp[i][1]])
    dataTmp = sorted(dataTmp)
    data = [dataTmp[0]]
    for d in dataTmp:
        if d[0] in ['2050A-2']:
            pass
        else:
            if d[1][3:5] != data[-1][1][3:5]:
                data.append(d)
    if data[-1][0] != '2490M':
        data.append(d)
    text = '当前生产机型：<br>'
#     for x in data:
# ##        text += 'OP' + x[0] + ':' + x[1] + '<br>'
#         text += x[0] + ':' + x[1] + '<br>'

    head = '''
            <table  border='-1'>
                <tr bgcolor='blue'>
                    <th>工位</th>
                    <th>发动机号</th>
                </tr>
                %s
            </table>
            '''
    body = ''
    for x in data:
        tmp = '''
        <tr align='center'>
            <td>%s</td>
            <td>%s</td>
        </tr>
        '''%(x[0],x[1])
        body = body + tmp
    text = head%body
    return text

def insertShiftHour(shiftHour,restday,jph,shifts,jph2):
    try:
        cnx = pymysql.connect('127.0.0.1','root','12345678',"assembly" )
        cur = cnx.cursor()
        sqlfun = "insert into assembly.shift_hour(shift,restday,jph,shifts,jph2)values(%f,%s,%d,%d,%d)"%(shiftHour,restday,jph,shifts,jph2)
        cur.execute(sqlfun)
        cnx.commit()
        cur.close()
        cnx.close()
    except Exception as e:
        print('eeeeeeeeeeeeeeeeeeeeeeeexxxxxxxxxxxxx')
        print(e)
        pass

        
    
if __name__ == '__main__':

    rateThread = Thread(target=run)
    rateThread.start()

    OP1100MAlertThread = Thread(target=sendOP1100MAlert)
    OP1100MAlertThread.start()

    keyParaAlertThread = Thread(target=keyParaAlert)
    keyParaAlertThread.start()
    

    sections = [
        ['LOOPA一组',
         'LOOPA三组',
         'LOOPA VCM返修',
         'LOOPA四组',
         'OP1100后面'],
        
        ['LOOPB一组',
         'LOOPB四组',
         '冷试前头'],

        ['长缸体返修']
        ]

    limits = [
        [[1,20,13,0],
         [72,89,0,10],
         [119,128,5,0],
         [104,119,0,6],
         [30,48,0,8]],

        [[133,142,6,0],
         [221,247,0,9],
         [154,182,0,10]],

        [[253,262,5,0]]
        ]


    fullCounts = []
    emptyCounts = []
    for ii in range(len(sections)):
        fullCountTmp = []
        emptyCountTmp = []
        for jj in range(len(sections[ii])):
            fullCountTmp.append(0)
            emptyCountTmp.append(0)
        fullCounts.append(fullCountTmp)
        emptyCounts.append(emptyCountTmp)
    

    access_token_alert = ''
    try:
        access_token_alert = getAccessToken_alert()
    except:
        pass
    access_token_FTQ = ''
    try:
        access_token_FTQ = getAccessToken_FTQ()
    except:
        pass
    personAlerts = ['2','5','12']
    textAlert = ''

    jph = getJPHTarget()
    hourCountTargets,rests = getHourTargetRests(jph)

    shiftChangedFlag = False
    shiftProductionSent = False
    curShift = 1
    nDiff = 0

    lastShiftHour,_,shifts,jph,jph2,single_shift_starttime,single_shift_dur = getShiftHour()
    print('current shiftHour is '+str(lastShiftHour))
    while 1:

        try:
            jph = getJPHTarget()
            hourCountTargets,rests = getHourTargetRests(jph)
            
            ret = 0
            curtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            curDate = datetime.datetime.now().strftime("%Y-%m-%d")
            yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
            curHour = int(datetime.datetime.now().strftime("%H"))
            curMinute = int(datetime.datetime.now().strftime("%M"))
            curSecond = int(datetime.datetime.now().strftime("%S"))
            curWeekDay = datetime.datetime.now().weekday()
            layout = getlayout()
            counts=layout[0][261:]
            shiftHour,restDays,shifts,jph,jph2,single_shift_starttime,single_shift_dur = getShiftHour()

            # curHour = 19
            # curMinute = 33

            if lastShiftHour != shiftHour:
                print('SHIFTHOUR CHANGED...CURRENT SHIFTHOUR IS '+str(shiftHour))
                lastShiftHour = shiftHour

                
            loopaCounts = hourCount(counts[:24],shiftHour)
            loopbCounts = hourCount(counts[24:],shiftHour)
            # loopaStr = str(loopaCounts).replace('[','').replace(']','')
            # loopbStr = str(loopbCounts).replace('[','').replace(']','')
            # loopaStr = 
            # loopbStr = 
            loopStr = makeloopStrTable(loopaCounts,loopbCounts,shiftHour,single_shift_starttime,single_shift_dur)

            rrr = 0

            hourCountTarget = hourCountTargets[shiftHour-8]

            rest = rests[shiftHour-8]


            if curHour > 7:
                rt = rest[curHour-7]
            else:
                rt = rest[curHour+17]

            if rt != 0:
                if curMinute <= rt[1]:
                    curHourTarget = ceil((curMinute*60+curSecond)/(3600/jph))
                elif curMinute >= rt[1] and curMinute < (rt[1] + rt[0]):
                    curHourTarget = ceil(rt[1]*60/(3600/jph))
                    rrr = 1
                else:
                    curHourTarget = ceil(((curMinute-rt[0])*60+curSecond)/(3600/jph))
            else:
                curHourTarget = ceil((curMinute*60+curSecond)/(3600/jph))
            print(curHour,rt,curHourTarget)

            curCountsA = sum(loopaCounts)
            curCounts = sum(loopbCounts)
            

            fxx = 0 #sum(hourCountTarget[int(curHour - 7 - single_shift_starttime + 20):(curHour-7)])
            if int(shiftHour) == shiftHour:
                if curHour > 7 and curHour < shiftHour:
                    curTarget = sum(hourCountTarget[:(curHour-7)]) + curHourTarget
                elif curHour >= shiftHour:
                    curTarget = sum(hourCountTarget[(shiftHour-7):(curHour-6)]) + curHourTarget
                    fxx = sum(hourCountTarget[(curHour-6 - int(single_shift_starttime) + 20):(curHour-6)])
                    # print("fxx1",fxx)
                else:
                    curTarget = sum(hourCountTarget[(shiftHour-7):(curHour+18)]) + curHourTarget
                    fxx = sum(hourCountTarget[int(curHour + 18 - single_shift_starttime + 20):(curHour+18)])
                    # print("fxx",fxx)


            else:
                if curHour > 7 and curHour < int(shiftHour):
                    curTarget = sum(hourCountTarget[:(curHour-7)]) + curHourTarget

                elif curHour == int(shiftHour) and curMinute < int(60*(shiftHour-int(shiftHour))):
                    curTarget = sum(hourCountTarget[:(curHour-7)]) + curHourTarget
                
                elif curHour == int(shiftHour) and curMinute >= int(60*(shiftHour-int(shiftHour))):
                    curHourTarget = ceil(((curMinute - int(60*(shiftHour-int(shiftHour))))*60+curSecond)/(3600/jph))
                    curTarget = curHourTarget

                elif curHour > shiftHour:
                    curTarget = sum(hourCountTarget[(ceil(shiftHour)-7):(curHour-6)]) + curHourTarget
                    print(hourCountTarget[(ceil(shiftHour)-7):(curHour-5)])

                elif curHour < 7:
                    curTarget = sum(hourCountTarget[(ceil(shiftHour)-7):(curHour+18)]) + curHourTarget

            # print("curTarget",curTarget)
            if single_shift_starttime is not None:
                curTarget = curTarget - fxx
            # print("curTarget",curTarget)
            countsDiff = curCounts - curTarget
            countsDiffA = curCountsA - curTarget

            print('当前产量：'+str(curCounts)+',目标：'+str(curTarget))
            
            if countsDiff > 0:
                info = '(%dJPH)<br>当前产量：%d，目标：%d.<br>超产%d台...稳的一批！！！<br>%s'%(jph,curCounts,curTarget,countsDiff,loopStr)
                digestmp = "超产%d台...稳的一批！！！"%countsDiff
            elif countsDiff < 0:
                info = '(%dJPH)<br>当前产量：%d，目标：%d.<br>欠产%d台了!!!!!!!!! <br>%s'%(jph,curCounts,curTarget,abs(countsDiff),loopStr)
                digestmp = "欠产%d台了!!!!!!!!"%abs(countsDiff)
            else:
                info = '(%dJPH)<br>当前产量：%d，目标：%d.<br>暂时没欠产，加油干！！！ <br>%s'%(jph,curCounts,curTarget,loopStr)
                digestmp = "暂时没欠产，加油干！！！"

            if curCounts-curTarget < 0:
                infoo = "%dJPH  TARGET:%d  CUR:%d  DIFF:%d"%(jph,curTarget,curCounts,curCounts-curTarget)
            elif curCounts-curTarget > 0:
                infoo = "%dJPH  TARGET:%d  CUR:%d  DIFF:%s"%(jph,curTarget,curCounts,'+'+str(curCounts-curTarget))
            else:
                infoo = "%dJPH  TARGET:%d  CUR:%d  DIFF:NA"%(jph,curTarget,curCounts)
            restDaysLen = len(restDays)
            restFlag = False
            if restDaysLen == 1:
                # if curWeekDay == restDays[0] and curHour >= ((shiftHour-8)*2+8-24):
                if (curWeekDay == restDays[0] and curHour == (int((shiftHour-8)*2)+8-24) and curMinute >= int((((shiftHour-8)*2+8-24)-(int((shiftHour-8)*2)+8-24))*60+1)) or (curWeekDay == restDays[0] and curHour > (int((shiftHour-8)*2)+8-24)):
                    restFlag = True
                # print("restFlag1:",restFlag)
            elif restDaysLen > 1:
                if curWeekDay in restDays[1:]:
                    restFlag = True
                    # print("restFlag2:",restFlag)
                elif curWeekDay == restDays[0] and curHour >= ((shiftHour-8)*2+8-24):
                    restFlag = True
                    # print("restFlag3:",restFlag)
                # print("restFlag33:",restFlag)
            if single_shift_starttime is not None:
                pass
            else:
                if (restDays[-1] + 1)%7 == curWeekDay and curHour < 8:
                    restFlag = True
            # print("restFlag4:",restFlag)
            if restDaysLen == 1 and restDays[0] == 9:
                restFlag = False
            # print("restFlag5:",restFlag)


            if restFlag == False and nDiff >= 3:

                try:
                    goodcount_1100,goodcount_1180,goodcount_1300 = getDiffs()
                    countsDiff_1100 = goodcount_1100 - curTarget
                    countsDiff_1180 = goodcount_1180 - curTarget
                    countsDiff_1300 = goodcount_1300 - curTarget
                    insertCountsDiff(countsDiffA,countsDiff,countsDiff_1100,countsDiff_1180,countsDiff_1300,rrr)
                    nDiff = 0
                except Exception as e:
                    print(e)

            # if (curHour in [8,shiftHour]) and curMinute < 1:
            #     restFlag = True
            
            # print("restFlag:",restFlag)
            if shifts == 1:
                if single_shift_starttime is not None:
                    print('xxx')
                    if ((curHour+curMinute/60) < single_shift_starttime and (curHour+curMinute/60) > 8)  or \
                        (curHour+curMinute/60) > single_shift_starttime + single_shift_dur or \
                        ((curHour+curMinute/60) < 8 and (curHour+curMinute/60) > (single_shift_starttime + single_shift_dur)%24):
                        restFlag = True
                else:
                    print('yyyy')
                    if (curHour+curMinute/60) > (shiftHour+1/90) or curHour < 8:
                        restFlag = True
                        print('one shift.......rest time......')

            if restFlag == False:

                if curHour > 8:
                    curShiftDate = curDate
                else:
                    curShiftDate = yesterday

                if curHour == int(shiftHour) and curMinute >= (60*(shiftHour - int(shiftHour))):
                    shiftChangedFlag = True
                    curShift = 2
                elif curHour == (int((shiftHour-8)*2)+8-24) and curMinute >= int((((shiftHour-8)*2+8-24)-(int((shiftHour-8)*2)+8-24))*60):
                    shiftChangedFlag = True
                    if curHour == 8:
                        curShift = 1
                    else:
                        curShift = 2
                else:
                    shiftChangedFlag = False

                if (curHour in [int(shiftHour-1),(int(2*shiftHour)-9)%24])  and shiftProductionSent == True:
                    shiftProductionSent = False

                if shiftChangedFlag == True and shiftProductionSent == False:
                    shiftChangedFlag = True
                    shiftProductionSent = sendShiftProduction(curShift,curShiftDate,shiftHour,jph,single_shift_starttime,shifts,single_shift_dur)

                    # sendStaRepairDiff(curShift,curShiftDate)

                    if shifts == 2 and jph2 != 0:
                        rrrrr = ''.join([str(x) for x in restDays])
                        insertShiftHour(shiftHour,rrrrr,jph2,shifts,jph)

            else:
                print('rest time......shiftProductionSent')

    ##        person = 'zhangsan'

            if (curHour < ((shiftHour-8)*2+8-24) or curHour > 7) and restFlag == False:
                for section,limit,personAlert,fullCount,emptyCount in zip(sections,limits,personAlerts,fullCounts,emptyCounts):
                    for i in range(len(section)):
                        checkStatus = check(layout,limit[i][0],limit[i][1],limit[i][2],limit[i][3])
                        if checkStatus[0]:
                            fullCount[i] += 1
                            if fullCount[i] >= 5:
                                #发送报警
            ##                    print(loopaSections[i] + '堵线啦~~~！！！')
                                curTypesInfo = getStaInfo()
                                textAlert = '#### 堵线空线预警 ####<br>%s<br><br>%s堵线啦~~~！！！<br><br>'%(curtime,section[i])
                                title = '%s堵线啦~~~！！！'%section[i]
                                digest = curtime + '\n' +digestmp
                                textAlert = textAlert + info +'<br><br>'+curTypesInfo+'<br>'
                                ftqTableStr = makeFtqTable()
                                textAlert = textAlert + ftqTableStr
                                print(textAlert)
                                try:
                                    access_token_alert = getAccessToken_alert()
                                    # errmsgAlert = starvBlockAlert(access_token_alert,personAlert,textAlert)
                                    errmsgAlert = starvBlockAlert(access_token_alert,personAlert,textAlert,digest,title)
                                except Exception as e:
                                    print(e)
                                # try:
                                #     errmsgAlert,ret = sendImage(access_token_alert,personAlert,'layout.png')
                                # except Exception as e:
                                #     print(e)
                                print(errmsgAlert)
                                if errmsgAlert != 'ok':
                                    access_token_alert = getAccessToken_alert()
                                    try:
                                        # starvBlockAlert(access_token_alert,personAlert,textAlert)
                                        errmsgAlert = starvBlockAlert(access_token_alert,personAlert,textAlert,digest,title)
                                    except Exception as e:
                                        print(e)
                                    # try:
                                    #     errmsgAlert,ret = sendImage(access_token_alert,personAlert,'layout.png')
                                    # except Exception as e:
                                    #     print(e)
                                fullCount[i] = -40
                        else:
                            if fullCount[i] < 0:
                                fullCount[i] += 1
                            else:
                                fullCount[i] = 0

                        if checkStatus[1]:
                            emptyCount[i] += 1
                            if emptyCount[i] >= 5:
                                #发送报警
            ##                    print(loopaSections[i] + '空线啦~~~！！！')
                                curTypesInfo = getStaInfo()
                                textAlert = '#### 堵线空线预警 ####<br>%s<br><br>%s空线啦~~~！！！<br><br>'%(curtime,section[i])
                                title = '%s空线啦~~~！！！'%section[i]
                                digest = curtime + '\n' +digestmp
                                textAlert = textAlert + info +'<br><br>'+curTypesInfo+'<br>'
                                ftqTableStr = makeFtqTable()
                                textAlert = textAlert + ftqTableStr
                                print(textAlert)
                                try:
                                    access_token_alert = getAccessToken_alert()
                                    errmsgAlert = starvBlockAlert(access_token_alert,personAlert,textAlert,digest,title)
                                except Exception as e:
                                    print(e)
                                # try:
                                #     errmsgAlert,ret = sendImage(access_token_alert,personAlert,'layout.png',infoo)
                                # except Exception as e:
                                #     print(e)
                                if errmsgAlert != 'ok':
                                    try:
                                        access_token_alert = getAccessToken_alert()
                                        # starvBlockAlert(access_token_alert,personAlert,textAlert)
                                        errmsgAlert = starvBlockAlert(access_token_alert,personAlert,textAlert,digest,title)
                                    except Exception as e:
                                        print(e)
                                    # try:
                                    #     errmsgAlert,ret = sendImage(access_token_alert,personAlert,'layout.png')
                                    # except Exception as e:
                                    #     print(e)
                                emptyCount[i] = -40
                        else:
                            if emptyCount[i] < 0:
                                emptyCount[i] += 1
                            else:
                                emptyCount[i] = 0
                
                
                print('ret:',ret)
                print(restFlag,(curHour < ((shiftHour-8)*2+8-24) or curHour > 7))
                print(fullCounts)
                print(emptyCounts)
                print('')
                nDiff += 1
            else:
                print('rest time......alerts...')
        except Exception as e:
            print('error:',e)
            

        time.sleep(40)



